package gov.pbc.nn.kjgl.software.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.bstek.bdf2.core.business.IUser;
import com.bstek.bdf2.core.context.ContextHolder;
import com.bstek.bdf2.core.orm.ParseResult;
import com.bstek.dorado.data.entity.EntityUtils;
import com.bstek.dorado.data.provider.Criteria;
import com.bstek.dorado.data.provider.Page;

import gov.pbc.nn.core.dao.AbstractHibernateDao;
import gov.pbc.nn.core.dao.AbstractJdbcDao;
import gov.pbc.nn.core.dao.PbcnnCoreHibernateDao;
import gov.pbc.nn.core.dao.PbcnnCoreJdbcDao;
import gov.pbc.nn.kjgl.entity.Project;
import gov.pbc.nn.uflo.entity.Opinion;

@Repository
public class QueryProjectDao extends AbstractJdbcDao {

	public void loadQueryProject(Page<Project> page, Criteria criteria) throws Exception {
		// "SELECT	T.* FROM (SELECT A.*, B.FILE_NAME_ AS YWXQS_ FROM t_project AS A  LEFT JOIN t_pfile AS B ON a.BUSINESS_REQUIREMENTS_ID_ = b.ID_	WHERE A.INVALID_ = 0 AND A.CREATE_USER_=:username order by w.CREATE_DATE_ desc 	) T WHERE	1 = 1 ";
		String sql = "SELECT T.* FROM (SELECT A.*, B.FILE_NAME_ AS YWXQS_ FROM t_project AS A  LEFT JOIN t_pfile AS B ON a.BUSINESS_REQUIREMENTS_ID_ = b.ID_	WHERE A.INVALID_ = 0 ) T WHERE	1 = 1 ";
		Map<String, Object> params = new HashMap<String, Object>();
		// params.put("username", ContextHolder.getLoginUser().getUsername());
		ParseResult pr = this.parseCriteria(criteria, true, "T");
		if (pr != null) {
			StringBuffer sbSql = pr.getAssemblySql();
			sql += " and " + sbSql.toString() + " order by T.CREATE_DATE_ desc";
			params.putAll(pr.getValueMap());
		} else {
			sql += " order by T.CREATE_DATE_ desc";
		}
		this.pagingQuery(page, sql, new ProjceRowMapper(), params);

	}

	public void loadQueryProjectByUsername(Page<Project> page, Criteria criteria) throws Exception {

		PbcnnCoreJdbcDao jdbc = ContextHolder.getBean(PbcnnCoreJdbcDao.BEAN_ID);
		// String sql =
		// " select distinct busKey from Opinion  where invalid=false  and username=:username and processName=:processName order by T.CREATE_DATE_ desc";
		String sql = " select distinct BUS_KEY_ from T_UFLO_OPINION  where INVALID_=0  and USERNAME_=? and PROCESS_NAME_=? order by CREATE_DATE_ desc";
		String username = ContextHolder.getLoginUser().getUsername();
		String processName = "立项申请";
		List<Map<String, Object>> queryForList = jdbc.getJdbcTemplate().queryForList(sql, new Object[] { username, processName });
		List<String> tempList = new ArrayList<String>();
		for (Map<String, Object> m : queryForList) {
			tempList.add((String) m.get("BUS_KEY_"));
		}
		// 根据id查找自己审批过的数据
		Object[] array = tempList.toArray();
		String sql2 = "SELECT T.* FROM (SELECT A.*, B.FILE_NAME_ AS YWXQS_ FROM t_project AS A  LEFT JOIN t_pfile AS B ON a.BUSINESS_REQUIREMENTS_ID_ = b.ID_	WHERE A.INVALID_ = 0   "
				+ parameterTo(array) + ") T WHERE	1 = 1 ";

		Map<String, Object> params = new HashMap<String, Object>();
		ParseResult pr = this.parseCriteria(criteria, true, "T");
		if (pr != null) {
			StringBuffer sbSql = pr.getAssemblySql();
			sql2 += " and " + sbSql.toString() + " order by T.CREATE_DATE_ desc";
			params.putAll(pr.getValueMap());
		} else {
			sql2 += " order by T.CREATE_DATE_ desc";
		}
		this.pagingQuery(page, sql2, new ProjceRowMapper(), params);
	}

	public String parameterTo(Object[] s) {
		 String sql = null;
		if (s.length > 0) {
			sql = "and A.ID_ in ( ";
			for (int i = 0; i < s.length; i++) {
				sql += "'" + s[i].toString() + "'";
				if (s.length - 1 != i) {
					sql += ",";
				}
			}
			sql += " ) ";
		} else {
			sql = "and A.ID_ in ('')";
		}
		return sql;
	}

	public class ProjceRowMapper implements RowMapper<Project> {
		@Override
		public Project mapRow(ResultSet rs, int rowNum) throws SQLException {
			Project a = new Project();
			a.setId(rs.getString("ID_"));
			a.setCreateUser(rs.getString("CREATE_USER_"));
			a.setCreateDate(rs.getDate("CREATE_DATE_"));
			a.setUpdateDate(rs.getDate("UPDATE_DATE_"));
			a.setUpdateUser(rs.getString("UPDATE_USER_"));
			a.setInvalid(rs.getBoolean("INVALID_"));
			a.setName(rs.getString("NAME_"));
			a.setProjectDept(rs.getString("PROJECT_DEPT_"));
			a.setProjectTime(rs.getDate("PROJECT_TIME_"));
			a.setDeliveryTime(rs.getDate("DELIVERY_TIME_"));
			a.setBudgetProject(rs.getDouble("BUDGET_PROJECT_"));
			a.setBusinessRequirementsId(rs.getString("BUSINESS_REQUIREMENTS_ID_"));
			a.setCallUser(rs.getString("CALL_USER_"));
			a.setCallUserPhone(rs.getString("CALL_USER_PHONE_"));
			a.setDeclareMeans(rs.getString("DECLARE_MEANS_"));
			a.setExternalParties(rs.getString("EXTERNAL_PARTIES_"));
			a.setKjDeptFirstOpinion(rs.getString("KJ_DEPT_FIRST_OPINION_"));
			a.setKjDirectorApproval(rs.getString("KJ_DIRECTOR_APPROVAL_"));
			a.setOtherId(rs.getString("OTHER_ID_"));
			a.setProjectBackground(rs.getString("PROJECT_BACKGROUND_"));
			a.setProjectContent(rs.getString("PROJECT_CONTENT_"));
			a.setProjectTarget(rs.getString("PROJECT_TARGET_"));
			a.setProjectType(rs.getString("PROJECT_TYPE_"));
			a.setTagHeadBank(rs.getInt("TAG_HEAD_BANK_"));
			a.setTagProject(rs.getInt("TAG_PROJECT_"));
			a.setXtztjsfaId(rs.getString("XTZTJSFA_ID_"));
			a.setXxhxmjysId(rs.getString("XXHXMJYS_ID_"));
			a.setYwDirectorApproval(rs.getString("YW_DIRECTOR_APPROVAL_"));
			a.setYwxqfxbgId(rs.getString("YWXQFXBG_ID_"));
			a.setDeptName(rs.getString("DEPT_NAME_"));
			a.setZgywhld(rs.getString("ZGYWHLD_"));
			a.setYwzgDept(rs.getString("YWZG_DEPT_"));
			a.setYwzgDeptId(rs.getString("YWZG_DEPT_ID_"));
			a.setState(rs.getInt("STATE_"));
			a.setZgywhldUsername(rs.getString("ZGYWHLD_USERNAME_"));
			a.setXqtcDept(rs.getString("XQTC_DEPT_"));
			a.setXqtcDeptId(rs.getString("XQTC_DEPT_ID_"));
			a.setHldqz(rs.getInt("HLDQZ_"));
			try {
				a = EntityUtils.toEntity(a);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			EntityUtils.setValue(a, "ywxqs", rs.getString("YWXQS_"));
			return a;
		}
	}
}
